import pymysql,os,time
from pymysql.cursors import DictCursor
from  selenium import webdriver

class Report:
    def __init__(self):
        #self.driver = webdriver.Chrome()
        self.db = pymysql.connect('192.168.1.146', 'root', '123456', 'Alpha', charset='utf8')
        self.cursor = self.db.cursor(DictCursor)

    def HTML_generate(self,version):

        #查询数据库中数据
        sql = "select * from report where version = '%s'" % version
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        print(results)


        #打开模板读取内容
        path = 'D:/python/WoniuBoss/report/template.html'
        template_file = open(path,'r',encoding='utf-8')
        content = template_file.read()

        #替换版本信息
        content = content.replace('$test-date','WoniuBoss')
        content = content.replace('覃幽兰','王博')
        version = results[0]['version']
        content = content.replace('$test-version',version)

        #统计所有用例数量
        sql_all = "select count(*) as count from report where version = '%s'" % version
        self.cursor.execute(sql_all)
        all_count = self.cursor.fetchall()[0]['count']
        print(str(all_count))
        content = content.replace('$case_count', str(all_count))

        #统计成功数量
        sql_pass = "select count(*) as count from report where version = '%s' and result = '成功'" % version
        self.cursor.execute(sql_pass)
        pass_count = self.cursor.fetchall()[0]['count']
        print(str(pass_count))
        content = content.replace('$pass-count',str(pass_count))

        # 统计失败数
        sql_fail = "select count(*) as count from report where version = '%s' and result = '失败'" % version
        self.cursor.execute(sql_fail)
        fail_count = self.cursor.fetchall()[0]['count']
        content = content.replace('$fail-count', str(fail_count))

        # 统计错误数
        sql_error = "select count(*) as count from report where version = '%s' and result = '错误'" % version
        self.cursor.execute(sql_error)
        error_count = self.cursor.fetchall()[0]['count']
        content = content.replace('$error-count', str(error_count))

        #最后时间
        sql_time = "select testtime from report where version = '%s' limit 1" % version
        self.cursor.execute(sql_time)
        lasttime = self.cursor.fetchall()
        print(lasttime[0]['testtime'])
        content = content.replace('$last-time',str(lasttime[0]['testtime']))

        test_result = ""

        for i in results:
            test_result += '<tr height="40">'
            test_result += '<td width="7%%">%d</td>' % i['id']
            test_result += '<td width="9%%">%s</td>' % i['module']
            test_result += '<td width="9%%">%s</td>' % i['testtype']
            test_result += '<td width="7%%">%s</td>' % i['caseid']
            test_result += '<td width="20%%">%s</td>' % i['casetitle']
            test_result += '<td width="7%%">%s</td>' % i['result']
            test_result += '<td width="16%%">%s</td>' % i['testtime']
            test_result += '<td width="15%%">%s</td>' % i['error']
            test_result += '<td width="10%%">%s</td>' % i['screenshot']
            test_result += '</tr>\n'

        content = content.replace('$test-result',test_result)
        #print(content)

        #写入报告
        nowtime = time.strftime("%Y%m%d_%H%M%S",time.localtime(time.time()))
        report_path = 'D:/python/WoniuBoss/report/'+ nowtime + '.html'
        report = open(report_path,'w',encoding='utf-8')
        report.write(content)

        #关闭文件和数据库
        report.close()
        self.cursor.close()
        self.db.close()

    #将测试结结果写入数据库
    def write_report(self,version,module,testtype,caseid,casetitle,result,error,screenshot):
        conn = pymysql.connect(user='root',passwd='123456',host='192.168.1.146',db='Alpha',charset='utf8')
        self.cursor = conn.cursor()
        testtime = time.strftime('%Y-%m-%d %H-%M-%S',time.localtime(time.time()))
        sql = "insert into report (version,module,testtype,caseid,casetitle,result,testtime,error,screenshot)"\
              " values('%s','%s','%s','%s','%s','%s','%s','%s','%s')" %(version,module,testtype,caseid,casetitle,result,testtime,error,screenshot)
        print(sql)
        self.cursor.execute(sql)
        conn.commit()
        self.cursor.close()
        conn.close()




if __name__ == '__main__':
    start = Report()
    #start.write_report('3.5','登录模块','接口测试','TC-001','正确的用户名密码登录','成功','无','无')
    start.HTML_generate(3.5)




